﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Vegetable.Service
{
    /// <summary>
    /// 表3生产情况调查表T_VegProSurvey通用操作类
    /// </summary>
    public class VegProSurveyServices : VegProSurveys
    {
        public Pager<List<VegProSurveyEntity>> GetPageList(VegProSurveyQuery query)
        {
            Pager<List<VegProSurveyEntity>> obj = new Pager<List<VegProSurveyEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_VegProSurvey where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<VegProSurveyEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表3生产情况调查表T_VegProSurvey分页列表失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_VegProSurvey where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                return false;
            }
        }
        /// <summary>
        /// 获取填报数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns></returns>
        public ResResult<List<VegProSurveyEntity>> GetInfo(string reportId)
        {
            ResResult<List<VegProSurveyEntity>> obj = new ResResult<List<VegProSurveyEntity>>();
            //判定是否生成过报表
            if (CheckRepCount(reportId))
            {
                //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                List<VegProSurveyEntity> templist = GetTabContent(reportId, 0);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                    obj.message = "数据返回成功！";
                }
            }
            else
            {
                try
                {
                    //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                    List<VegProSurveyEntity> templist = GetTabContent(reportId, 1);
                    obj.model = templist;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "数据返回成功！";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                    obj.message = "数据返回成功！";
                }
            }
            return obj;
        }
        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddInfo(List<VegProSurveyEntity> list)
        {
            string strSql = @"INSERT INTO dbo.T_VegProSurvey";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "SortCode,";
            strSql += "SortName,";
            strSql += "ItemCode,";
            strSql += "ItemName,";
            strSql += "LD_Area,";
            strSql += "LD_Product,";
            strSql += "XP_Area,";
            strSql += "XP_Product,";
            strSql += "DZP_Area,";
            strSql += "DZP_Product,";
            strSql += "WS_Area,";
            strSql += "WS_Product,";
            strSql += "Remark)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@SortCode,";
            strSql += "@SortName,";
            strSql += "@ItemCode,";
            strSql += "@ItemName,";
            strSql += "@LD_Area,";
            strSql += "@LD_Product,";
            strSql += "@XP_Area,";
            strSql += "@XP_Product,";
            strSql += "@DZP_Area,";
            strSql += "@DZP_Product,";
            strSql += "@WS_Area,";
            strSql += "@WS_Product,";
            strSql += "@Remark)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                try
                {
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    LogHelper.ErrorLog("新增表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.ErrorResult;

                }
            }
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public ResResult<int> UpdateInfo(List<VegProSurveyEntity> list)
        {

            ResResult<int> obj = new ResResult<int>();
            ReportServices reportServices = new ReportServices();
            if (list.Count > 0)
            {
                string strSql = @"UPDATE dbo.T_VegProSurvey set ";
                strSql += "ReportId = @ReportId,";
                strSql += "SortCode = @SortCode,";
                strSql += "SortName = @SortName,";
                strSql += "ItemCode = @ItemCode,";
                strSql += "ItemName = @ItemName,";
                strSql += "LD_Area = @LD_Area,";
                strSql += "LD_Product = @LD_Product,";
                strSql += "XP_Area = @XP_Area,";
                strSql += "XP_Product = @XP_Product,";
                strSql += "DZP_Area = @DZP_Area,";
                strSql += "DZP_Product = @DZP_Product,";
                strSql += "WS_Area = @WS_Area,";
                strSql += "WS_Product = @WS_Product,";
                strSql += "Remark = @Remark";
                strSql += " where Id = @Id";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        int result = dbCon.Execute(strSql, list);
                        int resultReport = reportServices.UpStatus(list[0].ReportId, "2", "").status;//更新当前报表状态为已填报
                        if (result > 0 && resultReport == 1)
                        {
                            obj.model = result;
                            obj.status = DefaultKey.SuccessResult;
                            obj.message = "更新成功！";
                        }
                        else
                        {
                            obj.model = result;
                            obj.status = DefaultKey.InfoResult;
                            obj.message = "更新失败！数量为:" + result;
                        }
                        dbCon.Dispose();//数据库连接释放bj;
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                }
            }
            return obj;
        }

        /// <summary>
        /// 获取报表内容
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="edittype">0的时候返回视图列表，不为0的时候返回数据库列表</param>
        /// <returns></returns>
        public List<VegProSurveyEntity> GetTabContent(string reportId, int edittype)
        {
            List<VegProSurveyEntity> obj = new List<VegProSurveyEntity>();
            string strSql = "";
            var paramlist = new DynamicParameters();
            if (!string.IsNullOrEmpty(reportId))
            {
                paramlist.Add("ReportId", reportId.ToString().Trim());
            }
            if (edittype == 0)
            {
                strSql = @"select * from dbo.V_VegProSurvey order by SortCode asc,ItemCode asc";
            }
            else
            {
                strSql = @"select * from dbo.T_VegProSurvey where ReportId=@ReportId order by SortCode asc,ItemCode asc";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj = dbCon.Query<VegProSurveyEntity>(strSql, paramlist).ToList();
                    if (edittype == 0)
                    {
                        foreach (var item in obj)
                        {
                            item.ReportId = reportId;
                            item.Id = CustomFun.GetGuid();
                        }
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 读取导入的Excel数据(年度)
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<VegProSurveyEntity> ExcelToModelYear(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<VegProSurveyEntity> list = new List<VegProSurveyEntity>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(2);//从第四行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    VegProSurveyEntity model = new VegProSurveyEntity(); ;
                    model.ItemName = row.GetCell(0).ToString();
                    if (row.GetCell(4).ToString() != null && row.GetCell(4).ToString() != "")
                    {
                        model.LD_Area = Convert.ToDecimal(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.LD_Area = null;
                    }
                    if (row.GetCell(5).ToString() != null && row.GetCell(5).ToString() != "")
                    {
                        model.LD_Product = Convert.ToDecimal(row.GetCell(5).ToString());
                    }
                    else
                    {
                        model.LD_Product = null;
                    }
                    if (row.GetCell(7).ToString() != null && row.GetCell(7).ToString() != "")
                    {
                        model.XP_Area = Convert.ToDecimal(row.GetCell(7).ToString());
                    }
                    else
                    {
                        model.XP_Area = null;
                    }
                    if (row.GetCell(8).ToString() != null && row.GetCell(8).ToString() != "")
                    {
                        model.XP_Product = Convert.ToDecimal(row.GetCell(8).ToString());
                    }
                    else
                    {
                        model.XP_Product = null;
                    }
                    if (row.GetCell(10).ToString() != null && row.GetCell(10).ToString() != "")
                    {
                        model.DZP_Area = Convert.ToDecimal(row.GetCell(10).ToString());
                    }
                    else
                    {
                        model.DZP_Area = null;
                    }
                    if (row.GetCell(11).ToString() != null && row.GetCell(11).ToString() != "")
                    {
                        model.DZP_Product = Convert.ToDecimal(row.GetCell(11).ToString());
                    }
                    else
                    {
                        model.DZP_Product = null;
                    }
                    if (row.GetCell(13).ToString() != null && row.GetCell(13).ToString() != "")
                    {
                        model.WS_Area = Convert.ToDecimal(row.GetCell(13).ToString());
                    }
                    else
                    {
                        model.WS_Area = null;
                    }
                    if (row.GetCell(14).ToString() != null && row.GetCell(14).ToString() != "")
                    {
                        model.WS_Product = Convert.ToDecimal(row.GetCell(14).ToString());
                    }
                    else
                    {
                        model.WS_Product = null;
                    }
                    if (row.GetCell(16).ToString() != null && row.GetCell(16).ToString() != "")
                    {
                        model.Remark = row.GetCell(16).ToString();
                    }
                    else
                    {
                        model.Remark = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 读取导入的Excel数据(季度)
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<VegProSurveyEntity> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<VegProSurveyEntity> list = new List<VegProSurveyEntity>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(2);//从第四行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                try
                {
                    for (int i = startRow; i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        VegProSurveyEntity model = new VegProSurveyEntity();
                        model.ItemName = row.GetCell(0).ToString();//种类名称
                        if (row.GetCell(4).ToString() != null && row.GetCell(4).ToString() != "")
                        {
                            model.LD_Area = Convert.ToDecimal(row.GetCell(4).ToString());
                        }
                        else
                        {
                            model.LD_Area = null;
                        }
                        if (row.GetCell(7).ToString() != null && row.GetCell(7).ToString() != "")
                        {
                            model.XP_Area = Convert.ToDecimal(row.GetCell(7).ToString());
                        }
                        else
                        {
                            model.XP_Area = null;
                        }
                        if (row.GetCell(10).ToString() != null && row.GetCell(10).ToString() != "")
                        {
                            model.DZP_Area = Convert.ToDecimal(row.GetCell(10).ToString());
                        }
                        else
                        {
                            model.DZP_Area = null;
                        }
                        if (row.GetCell(13).ToString() != null && row.GetCell(13).ToString() != "")
                        {
                            model.WS_Area = Convert.ToDecimal(row.GetCell(13).ToString());
                        }
                        else
                        {
                            model.WS_Area = null;
                        }
                        if (row.GetCell(16).ToString() != null && row.GetCell(16).ToString() != "")
                        {
                            model.Remark = row.GetCell(16).ToString();
                        }
                        else
                        {
                            model.Remark = null;
                        }
                        list.Add(model);
                    }
                }
                catch (Exception ex)
                {

                }
            }
            return list;
        }
        /// <summary>
        /// 导出数据(季度)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(string ReportId)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegProSurvey") as HSSFSheet;//读取模板所在sheet位置
            HSSFRow row = (HSSFRow)sheet.GetRow(2);//读取模板第三行
            var style = row.GetCell(2).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式
            List<VegProSurveyEntity> list = GetInfo(ReportId).model;//数据获取
            VegProSurveyEntity total = new VegProSurveyEntity() { LD_Area = 0, XP_Area = 0, DZP_Area = 0, WS_Area = 0 };
            for (int i = 0; i < list.Count; i++)
            {
                try
                {
                    if (list[i].SortCode.Length == 8)
                    {
                        total.LD_Area += list[i].LD_Area == null ? 0 : list[i].LD_Area;
                        total.XP_Area += list[i].XP_Area == null ? 0 : list[i].XP_Area;
                        total.DZP_Area += list[i].DZP_Area == null ? 0 : list[i].DZP_Area;
                        total.WS_Area += list[i].WS_Area == null ? 0 : list[i].WS_Area;
                    }
                    HSSFRow datarow = (HSSFRow)sheet.GetRow(i + 3);
                    datarow.CreateCell(1).SetCellValue(Common.GetDouble(list[i].LD_Area) + Common.GetDouble(list[i].XP_Area) + Common.GetDouble(list[i].DZP_Area) + Common.GetDouble(list[i].WS_Area));
                    datarow.CreateCell(4).SetCellValue(list[i].LD_Area == null ? "" : list[i].LD_Area.ToString());
                    datarow.CreateCell(7).SetCellValue(list[i].XP_Area == null ? "" : list[i].XP_Area.ToString());
                    datarow.CreateCell(10).SetCellValue(list[i].DZP_Area == null ? "" : list[i].DZP_Area.ToString());
                    datarow.CreateCell(13).SetCellValue(list[i].WS_Area == null ? "" : list[i].WS_Area.ToString());
                    datarow.CreateCell(16).SetCellValue(list[i].Remark == null ? "" : list[i].Remark.ToString());
                    for (int j = 1; j < 17; j++)
                    {
                        datarow.GetCell(j).CellStyle = style;
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(ex.ToString().Trim());
                }
            }
            HSSFRow TotalRow = (HSSFRow)sheet.GetRow(list.Count + 3);
            TotalRow.CreateCell(0).SetCellValue("合计");
            TotalRow.CreateCell(1).SetCellValue(Common.GetDouble(total.LD_Area) + Common.GetDouble(total.XP_Area) + Common.GetDouble(total.DZP_Area) + Common.GetDouble(total.WS_Area));
            TotalRow.CreateCell(4).SetCellValue(total.LD_Area == null ? "" : total.LD_Area.ToString());
            TotalRow.CreateCell(7).SetCellValue(total.XP_Area == null ? "" : total.XP_Area.ToString());
            TotalRow.CreateCell(10).SetCellValue(total.DZP_Area == null ? "" : total.DZP_Area.ToString());
            TotalRow.CreateCell(13).SetCellValue(total.WS_Area == null ? "" : total.WS_Area.ToString());
            TotalRow.CreateCell(16).SetCellValue(total.Remark == null ? "" : total.Remark.ToString());
            for (int j = 1; j < 17; j++)
            {
                TotalRow.GetCell(j).CellStyle = style;
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出数据(年度)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodYear(string ReportId)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegProSurveyYear") as HSSFSheet;//读取模板所在sheet位置
            HSSFRow row = (HSSFRow)sheet.GetRow(3);//读取模板第三行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式
            List<VegProSurveyEntity> list = GetInfo(ReportId).model;//数据获取
            VegProSurveyEntity total = new VegProSurveyEntity()
            {
                LD_Area = 0,
                XP_Area = 0,
                DZP_Area = 0,
                WS_Area = 0,
                DZP_Product = 0,
                LD_Product = 0,
                WS_Product = 0,
                XP_Product = 0
            };
            for (int i = 0; i < list.Count; i++)
            {
                try
                {
                    if (list[i].SortCode.Length == 8)
                    {
                        total.LD_Area += list[i].LD_Area == null ? 0 : list[i].LD_Area;
                        total.XP_Area += list[i].XP_Area == null ? 0 : list[i].XP_Area;
                        total.DZP_Area += list[i].DZP_Area == null ? 0 : list[i].DZP_Area;
                        total.WS_Area += list[i].WS_Area == null ? 0 : list[i].WS_Area;
                        total.LD_Product += list[i].LD_Product == null ? 0 : list[i].LD_Product;
                        total.XP_Product += list[i].XP_Product == null ? 0 : list[i].XP_Product;
                        total.DZP_Product += list[i].DZP_Product == null ? 0 : list[i].DZP_Product;
                        total.WS_Product += list[i].WS_Product == null ? 0 : list[i].WS_Product;
                    }
                    //row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式
                    HSSFRow datarow = (HSSFRow)sheet.GetRow(i + 3);
                    Double TotalArea = Common.GetDouble(list[i].LD_Area) + Common.GetDouble(list[i].XP_Area) + Common.GetDouble(list[i].DZP_Area) + Common.GetDouble(list[i].WS_Area);
                    Double TotalProduct = Common.GetDouble(list[i].LD_Product) + Common.GetDouble(list[i].XP_Product) + Common.GetDouble(list[i].DZP_Product) + Common.GetDouble(list[i].WS_Product);
                    datarow.CreateCell(1).SetCellValue(TotalArea);
                    datarow.CreateCell(2).SetCellValue(TotalProduct);
                    datarow.CreateCell(3).SetCellValue(Common.GetCF(Convert.ToDecimal(TotalProduct), Convert.ToDecimal(TotalArea)));
                    datarow.CreateCell(4).SetCellValue(list[i].LD_Area == null ? "" : list[i].LD_Area.ToString());
                    datarow.CreateCell(5).SetCellValue(list[i].LD_Product == null ? "" : list[i].LD_Product.ToString());
                    datarow.CreateCell(6).SetCellValue(Common.GetCF(list[i].LD_Product, list[i].LD_Area));
                    datarow.CreateCell(7).SetCellValue(list[i].XP_Area == null ? "" : list[i].XP_Area.ToString());
                    datarow.CreateCell(8).SetCellValue(list[i].XP_Product == null ? "" : list[i].XP_Product.ToString());
                    datarow.CreateCell(9).SetCellValue(Common.GetCF(list[i].XP_Product, list[i].XP_Area));
                    datarow.CreateCell(10).SetCellValue(list[i].DZP_Area == null ? "" : list[i].DZP_Area.ToString());
                    datarow.CreateCell(11).SetCellValue(list[i].DZP_Product == null ? "" : list[i].DZP_Product.ToString());
                    datarow.CreateCell(12).SetCellValue(Common.GetCF(list[i].DZP_Product, list[i].DZP_Area));
                    datarow.CreateCell(13).SetCellValue(list[i].WS_Area == null ? "" : list[i].WS_Area.ToString());
                    datarow.CreateCell(14).SetCellValue(list[i].WS_Product == null ? "" : list[i].WS_Product.ToString());
                    datarow.CreateCell(15).SetCellValue(Common.GetCF(list[i].WS_Product, list[i].WS_Area));
                    datarow.CreateCell(16).SetCellValue(list[i].Remark == null ? "" : list[i].Remark.ToString());
                    for (int j = 1; j < 17; j++)
                    {
                        datarow.GetCell(j).CellStyle = style;
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(ex.ToString().Trim());
                }
            }
            HSSFRow TotalRow = (HSSFRow)sheet.GetRow(list.Count + 3);
            var totalArea = total.LD_Area + total.DZP_Area + total.XP_Area + total.WS_Area;
            var totalProduct = total.LD_Product + total.DZP_Product + total.XP_Product + total.WS_Product;
            TotalRow.CreateCell(0).SetCellValue("合计");
            TotalRow.CreateCell(1).SetCellValue(totalArea.ToString());
            TotalRow.CreateCell(2).SetCellValue(totalProduct.ToString());
            TotalRow.CreateCell(3).SetCellValue(Common.GetCF(Convert.ToDecimal(totalProduct), Convert.ToDecimal(totalArea)));
            TotalRow.CreateCell(4).SetCellValue(total.LD_Area == null ? "" : total.LD_Area.ToString());
            TotalRow.CreateCell(5).SetCellValue(total.LD_Product == null ? "" : total.LD_Product.ToString());
            TotalRow.CreateCell(6).SetCellValue(Common.GetCF(total.LD_Product, total.LD_Area));
            TotalRow.CreateCell(7).SetCellValue(total.XP_Area == null ? "" : total.XP_Area.ToString());
            TotalRow.CreateCell(8).SetCellValue(total.XP_Product == null ? "" : total.XP_Product.ToString());
            TotalRow.CreateCell(9).SetCellValue(Common.GetCF(total.XP_Product, total.XP_Area));
            TotalRow.CreateCell(10).SetCellValue(total.DZP_Area == null ? "" : total.DZP_Area.ToString());
            TotalRow.CreateCell(11).SetCellValue(total.DZP_Product == null ? "" : total.DZP_Product.ToString());
            TotalRow.CreateCell(12).SetCellValue(Common.GetCF(total.DZP_Product, total.DZP_Area));
            TotalRow.CreateCell(13).SetCellValue(total.WS_Area == null ? "" : total.WS_Area.ToString());
            TotalRow.CreateCell(14).SetCellValue(total.WS_Product == null ? "" : total.WS_Product.ToString());
            TotalRow.CreateCell(15).SetCellValue(Common.GetCF(total.WS_Product, total.WS_Area));
            TotalRow.CreateCell(16).SetCellValue(total.Remark == null ? "" : total.Remark.ToString());
            for (int j = 1; j < 17; j++)
            {
                TotalRow.GetCell(j).CellStyle = style;
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        public override ResResult<int> UpdateInfo(VegProSurveyEntity model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_VegProSurvey set ";
            strSql += "LD_Area = @LD_Area,";
            strSql += "LD_Product = @LD_Product,";
            strSql += "XP_Area = @XP_Area,";
            strSql += "XP_Product = @XP_Product,";
            strSql += "DZP_Area = @DZP_Area,";
            strSql += "DZP_Product = @DZP_Product,";
            strSql += "WS_Area = @WS_Area,";
            strSql += "WS_Product = @WS_Product,";
            strSql += "Remark = @Remark";
            strSql += " where  ItemName = @ItemName ";
            strSql += " and  ReportId = @ReportId ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新表3生产情况调查表T_VegProSurvey对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 根据种类获取汇总列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<VegProSurveyStatsModel>> GetCodeStatsList(VegNowModel model)
        {
            DictServices dictServices = new DictServices();
            SetListServices setListServices = new SetListServices();
            List<VegProSurveyStatsModel> list = new List<VegProSurveyStatsModel>();
            ResResult<List<VegProSurveyStatsModel>> result = new ResResult<List<VegProSurveyStatsModel>>();
            var paramlist = new DynamicParameters();
            int OldFillInYear = 0;
            if (!string.IsNullOrEmpty(model.FillInYear))//时间
            {
                model.FillInYear = dictServices.GetKey("ReportYear", model.FillInYear); ;
                OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;//上一年
                if (OldFillInYear < 0)
                {
                    OldFillInYear = 0;
                }
                paramlist.Add("FillInYear", model.FillInYear);
                paramlist.Add("OldFillInYear", OldFillInYear);
            }
            if (!string.IsNullOrEmpty(model.Status))//报表状态
            {
                paramlist.Add("Status", model.Status);
            }
            if (!string.IsNullOrEmpty(model.ReportCode))//报表代码
            {
                paramlist.Add("ReportCode", model.ReportCode);
            }
            #region 当年统计数据
            string strNowSql = "select v.ItemCode";
            strNowSql += ",SUM(ISNULL(V.LD_Area, 0)) LD_Area  ";//--露地面积
            strNowSql += ",SUM(ISNULL(V.LD_Product, 0)) LD_Product  ";//--露地产量
            strNowSql += ",SUM(ISNULL(V.XP_Area, 0)) XP_Area  ";//--小棚面积
            strNowSql += ",SUM(ISNULL(V.XP_Product, 0)) XP_Product  ";//--小棚产量
            strNowSql += ",SUM(ISNULL(V.DZP_Area, 0)) DZP_Area  ";//--大中棚面积
            strNowSql += ",SUM(ISNULL(V.DZP_Product, 0)) DZP_Product  ";//--大中棚产量
            strNowSql += ",SUM(ISNULL(V.WS_Area, 0)) WS_Area  ";//--温室面积
            strNowSql += ",SUM(ISNULL(V.WS_Product, 0)) WS_Product  ";//--温室产量
            strNowSql += "from[dbo].[T_Report]  r  ";//
            strNowSql += "left join [dbo].[T_VegProSurvey] v  on r.Id=v.ReportId  ";//
            strNowSql += "left join [dbo].[T_Area] a  on r.AreaCode=a.Code  ";//
            strNowSql += "where ReportCode=@ReportCode  ";//--报表代码 
            strNowSql += "and FillInYear=@FillInYear  ";//--年度
            if (!string.IsNullOrEmpty(model.AreaCode) && model.AreaCode != DefaultKey.ProvinceCode)//地区代码
            {
                paramlist.Add("AreaCode", model.AreaCode);
                strNowSql += "and a.ParentCode=@AreaCode  ";//--州市代码
            }
            if (!string.IsNullOrEmpty(model.TimesCycle))
            {
                paramlist.Add("TimesCycle", model.TimesCycle);
                strNowSql += "and r.TimesCycle=@TimesCycle  ";//--季度信息
            }
            strNowSql += "and v.ItemCode=@ItemCode  ";//--统计种类
            strNowSql += "and r.Status=@Status  ";//--报表状态(审核通过)";
            strNowSql += "group by ItemCode  ";//--分组;
            #endregion
            #region 上一年统计数据
            string strOldSql = "select v.ItemCode";
            strOldSql += ",SUM(ISNULL(V.LD_Area, 0)) LD_Area  ";//--露地面积
            strOldSql += ",SUM(ISNULL(V.LD_Product, 0)) LD_Product  ";//--露地产量
            strOldSql += ",SUM(ISNULL(V.XP_Area, 0)) XP_Area  ";//--小棚面积
            strOldSql += ",SUM(ISNULL(V.XP_Product, 0)) XP_Product  ";//--小棚产量
            strOldSql += ",SUM(ISNULL(V.DZP_Area, 0)) DZP_Area  ";//--大中棚面积
            strOldSql += ",SUM(ISNULL(V.DZP_Product, 0)) DZP_Product  ";//--大中棚产量
            strOldSql += ",SUM(ISNULL(V.WS_Area, 0)) WS_Area  ";//--温室面积
            strOldSql += ",SUM(ISNULL(V.WS_Product, 0)) WS_Product  ";//--温室产量
            strOldSql += "from[dbo].[T_Report]  r  ";//
            strOldSql += "left join [dbo].[T_VegProSurvey] v  on r.Id=v.ReportId  ";//
            strOldSql += "left join [dbo].[T_Area] a  on r.AreaCode=a.Code  ";//
            strOldSql += "where ReportCode=@ReportCode  ";//--报表代码 
            strOldSql += "and FillInYear=@OldFillInYear  ";//--年度
            if (!string.IsNullOrEmpty(model.AreaCode) && model.AreaCode != DefaultKey.ProvinceCode)//地区代码
            {
                paramlist.Add("AreaCode", model.AreaCode);
                strOldSql += "and a.ParentCode=@AreaCode  ";//--州市代码
            }
            if (!string.IsNullOrEmpty(model.TimesCycle))
            {
                paramlist.Add("TimesCycle", model.TimesCycle);
                strOldSql += "and r.TimesCycle=@TimesCycle  ";//--季度信息
            }
            strOldSql += "and v.ItemCode=@ItemCode  ";//--统计种类
            strOldSql += "and r.Status=@Status  ";//--报表状态(审核通过)";
            strOldSql += "group by ItemCode  ";//--分组;
            #endregion
            string strSql = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.ItemCode = tt.ItemCode) order by t.ItemCode";
            var VegTypeList = setListServices.GetList().model;
            try
            {
                if (VegTypeList != null)//种类
                {
                    VegProSurveyStatsModel StatsModelTotal = new VegProSurveyStatsModel
                    {
                        DZP_Area = 0,
                        DZP_Product = 0,
                        LD_Area = 0,
                        LD_Product = 0,
                        OldDZP_Area = 0,
                        OldDZP_Product = 0,
                        OldLD_Area = 0,
                        OldLD_Product = 0,
                        OldWS_Area = 0,
                        OldWS_Product = 0,
                        OldXP_Area = 0,
                        OldXP_Product = 0,
                        Veg_Type = "合计",
                        WS_Area = 0,
                        WS_Product = 0,
                        XP_Area = 0,
                        XP_Product = 0
                    };
                    foreach (var item in VegTypeList)
                    {
                        VegProSurveyStatsModel StatsModel = new VegProSurveyStatsModel();
                        if (!string.IsNullOrEmpty(item.ItemCode) && item.ParentCode != "VegProSurvey")
                        {
                            paramlist.Add("ItemCode", item.ItemCode);
                            using (var dbCon = ConnectFactory.GetOpenConnection())
                            {
                                //查询去年数据
                                StatsModel = dbCon.Query<VegProSurveyStatsModel>(strSql, paramlist).FirstOrDefault();
                            }
                            if (StatsModel != null)
                            {
                                StatsModel.Veg_Type = item.ItemName;//种类
                                if (item.ItemCode.Length == 8)//当为总和时
                                {
                                    StatsModelTotal.DZP_Area += StatsModel.DZP_Area == null ? 0 : StatsModel.DZP_Area;
                                    StatsModelTotal.DZP_Product += StatsModel.DZP_Product == null ? 0 : StatsModel.DZP_Product;
                                    StatsModelTotal.LD_Area += StatsModel.LD_Area == null ? 0 : StatsModel.LD_Area; ;
                                    StatsModelTotal.LD_Product += StatsModel.LD_Product == null ? 0 : StatsModel.LD_Product;
                                    StatsModelTotal.OldDZP_Area += StatsModel.OldDZP_Area == null ? 0 : StatsModel.OldDZP_Area;
                                    StatsModelTotal.OldDZP_Product += StatsModel.OldDZP_Product == null ? 0 : StatsModel.OldDZP_Product;
                                    StatsModelTotal.OldLD_Area += StatsModel.OldLD_Area == null ? 0 : StatsModel.OldLD_Area;
                                    StatsModelTotal.OldLD_Product += StatsModel.OldLD_Product == null ? 0 : StatsModel.OldLD_Product;
                                    StatsModelTotal.OldWS_Area += StatsModel.OldWS_Area == null ? 0 : StatsModel.OldWS_Area;
                                    StatsModelTotal.OldWS_Product += StatsModel.OldWS_Product == null ? 0 : StatsModel.OldWS_Product;
                                    StatsModelTotal.OldXP_Area += StatsModel.OldXP_Area == null ? 0 : StatsModel.OldXP_Area;
                                    StatsModelTotal.OldXP_Product += StatsModel.OldXP_Product == null ? 0 : StatsModel.OldXP_Product;
                                    StatsModelTotal.WS_Area += StatsModel.WS_Area == null ? 0 : StatsModel.WS_Area;
                                    StatsModelTotal.WS_Product += StatsModel.WS_Product == null ? 0 : StatsModel.WS_Product;
                                    StatsModelTotal.XP_Area += StatsModel.XP_Area == null ? 0 : StatsModel.XP_Area;
                                    StatsModelTotal.XP_Product += StatsModel.XP_Product == null ? 0 : StatsModel.XP_Product;
                                }
                            }
                        }
                        else
                        {
                            StatsModel = null;
                        }
                        if (StatsModel != null)
                        {
                            list.Add(StatsModel);
                        }
                    }
                    if (list.Count() > 0)//列表不为空
                    {
                        list.Add(StatsModelTotal);//添加合计到列表
                    }
                }
                result.model = list;
                result.status = DefaultKey.SuccessResult;
                result.message = "VegProSurvey报表统计数据获取成功";
            }


            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "VegProSurvey报表统计数据获取失败" + ex.ToString().Trim();
            }
            return result;
        }
        /// <summary>
        /// 根据地区获取种类列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<VegProSurveyStatsModel>> GetAreaStatsList(VegNowModel model)
        {
            ResResult<List<VegProSurveyStatsModel>> result = new ResResult<List<VegProSurveyStatsModel>>();
            DictServices dictServices = new DictServices();
            var paramlist = new DynamicParameters();
            int OldFillInYear = 0;
            if (!string.IsNullOrEmpty(model.AreaCode))
            {
                paramlist.Add("AreaCode", model.AreaCode);
            }
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetKey("ReportYear", model.FillInYear); ;
                OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;//上一年
                if (OldFillInYear < 0)
                {
                    OldFillInYear = 0;
                }
                paramlist.Add("FillInYear", model.FillInYear);
                paramlist.Add("OldFillInYear", OldFillInYear);
            }
            if (!string.IsNullOrEmpty(model.Status))
            {
                paramlist.Add("Status", model.Status);
            }
            #region 当年统计数据
            string strNowSql = "select Area.Code AreaCode,Dict.DictText FillYear, FullName AreaName,Coord,";
            strNowSql += "ISNULL(LD_Area, 0.00)  LD_Area,           ";// --露地蔬菜地占地面积
            strNowSql += "ISNULL(LD_Product, 0.00)  LD_Product,           ";  //--露地蔬菜产量
            strNowSql += "ISNULL(XP_Area, 0.00) XP_Area,   ";//     --小棚户蔬菜占地面积
            strNowSql += "ISNULL(XP_Product, 0.00) XP_Product, ";// --小棚户蔬菜产量
            strNowSql += "ISNULL(DZP_Area, 0.00) DZP_Area ,   ";//  --大中棚蔬菜占地面积
            strNowSql += "ISNULL(DZP_Product, 0.00)  DZP_Product, ";// --大中棚蔬菜产量
            strNowSql += "ISNULL(WS_Area, 0.00)  WS_Area    ,    ";//  --温室蔬菜占地面积
            strNowSql += "ISNULL(WS_Product, 0.00)  WS_Product    ";//--温室蔬菜产量
            strNowSql += "from(    ";
            strNowSql += "select     ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode,    ";
            }
            else
            {
                strNowSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code,    ";
            }
            strNowSql += " SUM(ISNULL(VegProSurvey.LD_Area, 0.00))  LD_Area,              ";// --露地蔬菜地占地面积
            strNowSql += "SUM(ISNULL(VegProSurvey.LD_Product, 0.00))  LD_Product,      ";// --露地蔬菜产量
            strNowSql += "SUM(ISNULL(VegProSurvey.XP_Area, 0.00)) XP_Area,          ";//--小棚户蔬菜占地面积
            strNowSql += "SUM(ISNULL(VegProSurvey.XP_Product, 0.00)) XP_Product,    ";// --小棚户蔬菜产量
            strNowSql += "SUM(ISNULL(VegProSurvey.DZP_Area, 0.00)) DZP_Area ,       ";// --大中棚蔬菜占地面积
            strNowSql += "SUM(ISNULL(VegProSurvey.DZP_Product, 0.00))  DZP_Product,     ";// --大中棚蔬菜产量
            strNowSql += "SUM(ISNULL(VegProSurvey.WS_Area, 0.00))  WS_Area    ,        ";// --温室蔬菜占地面积
            strNowSql += "SUM(ISNULL(VegProSurvey.WS_Product, 0.00))  WS_Product    ";// --温室蔬菜产量
            strNowSql += "from[dbo].[T_Report] Report    ";
            strNowSql += "left join[dbo].[T_VegProSurvey] VegProSurvey on Report.Id = VegProSurvey.ReportId    ";
            strNowSql += "right join T_Area Area on Area.Code = Report.AreaCode    ";
            strNowSql += "where    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "Area.ParentCode! = @AreaCode    ";
            }
            else
            {
                strNowSql += "Area.ParentCode = @AreaCode    ";
            }
            if (!string.IsNullOrEmpty(model.ReportCode))
            {
                paramlist.Add("ReportCode", model.ReportCode);
                strNowSql += "and ReportCode = @ReportCode   ";
                //strNowSql += "and len(ItemCode)=8   ";//不包含其中项目
            }
            strNowSql += "and len(VegProSurvey.ItemCode) = 8    ";
            strNowSql += "and Report.FillInYear = @FillInYear    ";
            strNowSql += "and Report.Status = @Status     "; //--表示审核通过
            if (!string.IsNullOrEmpty(model.ItemCode) && model.ItemCode != "1")
            {
                paramlist.Add("ItemCode", model.ItemCode);
                strNowSql += "and VegProSurvey.SortCode=@ItemCode   ";
            }
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "group by rollup(Area.ParentCode)    ";
            }
            else
            {
                strNowSql += "group by rollup(Area.Code)    ";
            }
            strNowSql += ") tt1    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "right join T_Area  Area on Area.Code=tt1.ParentCode       ";
            }
            else
            {
                strNowSql += "right join T_Area Area  on tt1.Code=Area.Code   ";
            }
            strNowSql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear    ";
            strNowSql += "where Area.ParentCode = @AreaCode  or Area.Code = @AreaCode    ";
            #endregion
            #region 上一年统计数据
            string strOldSql = "select Area.Code AreaCode,Dict.DictText FillYear, FullName AreaName,Coord,";
            strOldSql += "ISNULL(OldLD_Area, 0.00)  OldLD_Area,           ";// --露地蔬菜地占地面积
            strOldSql += "ISNULL(OldLD_Product, 0.00)  OldLD_Product,           ";  //--露地蔬菜产量
            strOldSql += "ISNULL(OldXP_Area, 0.00) OldXP_Area,   ";//     --小棚户蔬菜占地面积
            strOldSql += "ISNULL(OldXP_Product, 0.00) OldXP_Product, ";// --小棚户蔬菜产量
            strOldSql += "ISNULL(OldDZP_Area, 0.00) OldDZP_Area ,   ";//  --大中棚蔬菜占地面积
            strOldSql += "ISNULL(OldDZP_Product, 0.00)  OldDZP_Product, ";// --大中棚蔬菜产量
            strOldSql += "ISNULL(OldWS_Area, 0.00)  OldWS_Area    ,    ";//  --温室蔬菜占地面积
            strOldSql += "ISNULL(OldWS_Product, 0.00)  OldWS_Product    ";//--温室蔬菜产量
            strOldSql += "from(    ";
            strOldSql += "select     ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strOldSql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode,    ";
            }
            else
            {
                strOldSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code,    ";
            }
            strOldSql += " SUM(ISNULL(VegProSurvey.LD_Area, 0.00))  OldLD_Area,              ";// --露地蔬菜地占地面积
            strOldSql += "SUM(ISNULL(VegProSurvey.LD_Product, 0.00))  OldLD_Product,      ";// --露地蔬菜产量
            strOldSql += "SUM(ISNULL(VegProSurvey.XP_Area, 0.00)) OldXP_Area,          ";//--小棚户蔬菜占地面积
            strOldSql += "SUM(ISNULL(VegProSurvey.XP_Product, 0.00)) OldXP_Product,    ";// --小棚户蔬菜产量
            strOldSql += "SUM(ISNULL(VegProSurvey.DZP_Area, 0.00)) OldDZP_Area ,       ";// --大中棚蔬菜占地面积
            strOldSql += "SUM(ISNULL(VegProSurvey.DZP_Product, 0.00))  OldDZP_Product,     ";// --大中棚蔬菜产量
            strOldSql += "SUM(ISNULL(VegProSurvey.WS_Area, 0.00))  OldWS_Area    ,        ";// --温室蔬菜占地面积
            strOldSql += "SUM(ISNULL(VegProSurvey.WS_Product, 0.00))  OldWS_Product    ";// --温室蔬菜产量
            strOldSql += "from[dbo].[T_Report] Report    ";
            strOldSql += "left join[dbo].[T_VegProSurvey] VegProSurvey on Report.Id = VegProSurvey.ReportId    ";
            strOldSql += "right join T_Area Area on Area.Code = Report.AreaCode    ";
            strOldSql += "where    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strOldSql += "Area.ParentCode! = @AreaCode    ";
            }
            else
            {
                strOldSql += "Area.ParentCode = @AreaCode    ";
            }
            if (!string.IsNullOrEmpty(model.ReportCode))
            {
                paramlist.Add("ReportCode", model.ReportCode);
                strOldSql += "and ReportCode = @ReportCode   ";
                //strOldSql += " and len(ItemCode)=8   ";//不包含其中项目
            }
            strOldSql += "and len(VegProSurvey.ItemCode) = 8    ";
            strOldSql += "and Report.FillInYear = @OldFillInYear    ";
            strOldSql += "and Report.Status = @Status     "; //--表示审核通过
            if (!string.IsNullOrEmpty(model.ItemCode) && model.ItemCode != "1")
            {
                paramlist.Add("ItemCode", model.ItemCode);
                strOldSql += "and VegProSurvey.SortCode=@ItemCode   ";
            }
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strOldSql += "group by rollup(Area.ParentCode)    ";
            }
            else
            {
                strOldSql += "group by rollup(Area.Code)    ";
            }
            strOldSql += ") tt1    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strOldSql += "right join T_Area  Area on Area.Code=tt1.ParentCode       ";
            }
            else
            {
                strOldSql += "right join T_Area Area  on tt1.Code=Area.Code   ";
            }
            strOldSql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @OldFillInYear    ";
            strOldSql += "where Area.ParentCode = @AreaCode  or Area.Code = @AreaCode    ";
            #endregion
            string strCitySql = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.AreaCode = tt.AreaCode) order by t.AreaCode";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //查询去年数据
                    result.model = dbCon.Query<VegProSurveyStatsModel>(strCitySql, paramlist).ToList();
                    result.status = DefaultKey.SuccessResult;//初始化成功status为1
                    result.message = "VegBasicInfo报表统计数据获取成功！";
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "VegBasicInfo报表统计数据获取失败" + ex.ToString().Trim();
            }
            return result;
        }
        public ResResult<List<VegProSurveyStatsModel>> GetStatsList(VegNowModel model)
        {
            ResResult<List<VegProSurveyStatsModel>> result = new ResResult<List<VegProSurveyStatsModel>>();
            if (!string.IsNullOrEmpty(model.ItemCode))
            {
                result = GetAreaStatsList(model);//获取每个种类的地区汇总数据
            }
            else
            {
                result = GetCodeStatsList(model);//获取种类汇总代码
            }
            return result;
        }
        public MemoryStream MethodStats(VegNowModel model)
        {
            AreaServices areaServices = new AreaServices();
            //model.FillInYear = dict.GetKey(model.FillInYear);
            string ProvinceCode = DefaultKey.ProvinceCode;
            FileStream file = null;
            string FillInYear = model.FillInYear;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegStatsDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet(model.ReportCode) as HSSFSheet;//读取模板所在sheet位置
            List<VegProSurveyStatsModel> list = new List<VegProSurveyStatsModel>();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, model, AreaName);
            if (model.AreaCode == ProvinceCode)
            {
                model.FillInYear = FillInYear;
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                var First = GetStatsList(model).model;
                foreach (var item in areaList)
                {
                    if (item != ProvinceCode)
                    {
                        model.AreaCode = item;
                        model.FillInYear = FillInYear;
                        AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
                        getExcelStatsList(workbook, model, AreaName);
                    }
                }
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, VegNowModel model, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegStatsDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet(model.ReportCode) as HSSFSheet;//读取模板所在sheet位置
            List<VegProSurveyStatsModel> list = new List<VegProSurveyStatsModel>();
            list = GetStatsList(model).model;
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(1).CellStyle;//读取模板第二行第一单元格样式并保存
            if (list != null)
            {
                if (model.ReportCode == "VegProSurvey")
                {//季度
                    for (int i = 0; i < list.Count; i++)
                    {
                        var TotalArea = Common.GetDecimal(list[i].LD_Area) + Common.GetDecimal(list[i].XP_Area) + Common.GetDecimal(list[i].DZP_Area) + Common.GetDecimal(list[i].WS_Area);
                        var OldTotalArea = Common.GetDecimal(list[i].OldLD_Area) + Common.GetDecimal(list[i].OldXP_Area) + Common.GetDecimal(list[i].OldDZP_Area) + Common.GetDecimal(list[i].OldWS_Area);
                        HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                        datarow.CreateCell(0).SetCellValue(list[i].Veg_Type == null ? "" : list[i].Veg_Type.ToString());//地区
                        datarow.CreateCell(1).SetCellValue(Common.GetADecimal(TotalArea));//常年蔬菜占地面积
                        datarow.CreateCell(2).SetCellValue(Common.GetADecimal(OldTotalArea));
                        datarow.CreateCell(3).SetCellValue(Common.GetDivision(OldTotalArea, TotalArea));//增减比%
                        datarow.CreateCell(4).SetCellValue(list[i].LD_Area == null ? "" : Common.GetADecimal(list[i].LD_Area));//冬春季蔬菜面积
                        datarow.CreateCell(5).SetCellValue(list[i].OldLD_Area == null ? "" : Common.GetADecimal(list[i].OldLD_Area));
                        datarow.CreateCell(6).SetCellValue(Common.GetDivision(list[i].OldLD_Area, list[i].LD_Area));//增减比%
                        datarow.CreateCell(7).SetCellValue(list[i].XP_Area == null ? "" : Common.GetADecimal(list[i].XP_Area));//春夏季蔬菜面积
                        datarow.CreateCell(8).SetCellValue(list[i].OldXP_Area == null ? "" : Common.GetADecimal(list[i].OldXP_Area));//
                        datarow.CreateCell(9).SetCellValue(Common.GetDivision(list[i].OldXP_Area, list[i].XP_Area));//增减比%
                        datarow.CreateCell(10).SetCellValue(list[i].DZP_Area == null ? "" : Common.GetADecimal(list[i].DZP_Area));//菜地喷滴灌面积
                        datarow.CreateCell(11).SetCellValue(list[i].OldDZP_Area == null ? "" : Common.GetADecimal(list[i].OldDZP_Area));//
                        datarow.CreateCell(12).SetCellValue(Common.GetDivision(list[i].OldDZP_Area, list[i].DZP_Area));//增减比
                        datarow.CreateCell(13).SetCellValue(list[i].WS_Area == null ? "" : Common.GetADecimal(list[i].WS_Area));//水生蔬菜面积
                        datarow.CreateCell(14).SetCellValue(list[i].OldWS_Area == null ? "" : Common.GetADecimal(list[i].OldWS_Area));//
                        datarow.CreateCell(15).SetCellValue(Common.GetDivision(list[i].OldWS_Area, list[i].WS_Area));//增减比
                        for (int j = 0; j < 16; j++)
                        {
                            datarow.GetCell(j).CellStyle = style;
                        }
                    }
                }
                else if (model.ReportCode == "VegProSurveyYear")
                {//年度
                    for (int i = 0; i < list.Count; i++)
                    {
                        var TotalArea = Common.GetDecimal(list[i].LD_Area) + Common.GetDecimal(list[i].XP_Area) + Common.GetDecimal(list[i].DZP_Area) + Common.GetDecimal(list[i].WS_Area);
                        var TotalProduct = Common.GetDecimal(list[i].LD_Product) + Common.GetDecimal(list[i].XP_Product) + Common.GetDecimal(list[i].DZP_Product) + Common.GetDecimal(list[i].WS_Product);
                        HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                        if (!string.IsNullOrEmpty(model.ItemCode))
                        {
                            datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());//地区
                        }
                        else
                        {
                            datarow.CreateCell(0).SetCellValue(list[i].Veg_Type == null ? "" : list[i].Veg_Type.ToString());//种类
                        }
                        datarow.CreateCell(1).SetCellValue(Common.GetADecimal(TotalArea));//冬春季蔬菜面积
                        datarow.CreateCell(2).SetCellValue(Common.GetADecimal(TotalProduct));
                        datarow.CreateCell(3).SetCellValue(Common.GetCF(TotalProduct, TotalArea));//单产
                        datarow.CreateCell(4).SetCellValue(list[i].LD_Area == null ? "" : Common.GetADecimal(list[i].LD_Area));//露地蔬菜面积
                        datarow.CreateCell(5).SetCellValue(list[i].LD_Product == null ? "" : Common.GetADecimal(list[i].LD_Product));//
                        datarow.CreateCell(6).SetCellValue(Common.GetCF(list[i].LD_Product, list[i].LD_Area));//单产
                        datarow.CreateCell(7).SetCellValue(list[i].XP_Area == null ? "" : Common.GetADecimal(list[i].XP_Area));//小棚蔬菜面积
                        datarow.CreateCell(8).SetCellValue(list[i].XP_Product == null ? "" : Common.GetADecimal(list[i].XP_Product));//
                        datarow.CreateCell(9).SetCellValue(Common.GetCF(list[i].XP_Product, list[i].XP_Area));//单产
                        datarow.CreateCell(10).SetCellValue(list[i].DZP_Area == null ? "" : Common.GetADecimal(list[i].DZP_Area));//大中棚面积
                        datarow.CreateCell(11).SetCellValue(list[i].DZP_Product == null ? "" : Common.GetADecimal(list[i].DZP_Product));//
                        datarow.CreateCell(12).SetCellValue(Common.GetCF(list[i].DZP_Product, list[i].DZP_Area));//单产
                        datarow.CreateCell(13).SetCellValue(list[i].WS_Area == null ? "" : Common.GetADecimal(list[i].WS_Area));//温室蔬菜面积
                        datarow.CreateCell(14).SetCellValue(list[i].WS_Product == null ? "" : Common.GetADecimal(list[i].WS_Product));//
                        datarow.CreateCell(15).SetCellValue(Common.GetCF(list[i].WS_Product, list[i].WS_Area));//单产
                        for (int j = 0; j < 16; j++)
                        {
                            datarow.GetCell(j).CellStyle = style;
                        }
                    }
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}